package com.css.fxfzypg.modules.statistics.repository.impl;

import com.css.fxfzypg.manage.building.repository.entity.BuildingEntity;
import com.css.fxfzypg.modules.statistics.repository.StatisticsRepository;
import com.css.fxfzypg.modules.statistics.vo.QueryParams;
import com.css.fxfzypg.modules.statistics.vo.ResultVo;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class StatisticsRepositoryImpl implements StatisticsRepository {

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public List<ResultVo> getKilometreGridByGeomText(String geomText) {
        try {
            String sql = "select province,city,county area,sum(population) population ,sum(coveredarea) coveredarea,sum(gdp) gdp from data_pbe where isdelete='0' and ST_Intersects(geom,(st_geometryfromtext('"+geomText+"',4490))) GROUP BY province,city,county";
            List<ResultVo> resultVo = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultVo.class));
            return resultVo;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public List<ResultVo> getSlopeByGeomText(String geomText) {
        StringBuilder sql = new StringBuilder("select slope_number slopeNumber,st_area(ST_Transform(st_intersection((st_geometryfromtext('"+geomText+"',4490)),geom),4527)) stArea from sys_slope_union where ST_Intersects(geom,(st_geometryfromtext('"+geomText+"',4490))) GROUP BY slope_number,geom ORDER BY slope_number");
        List<ResultVo> resultVoList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResultVo.class));
        return resultVoList;
    }

    @Override
    public List<ResultVo> getSlopeByDivision(QueryParams queryParams) {
        StringBuilder sql = new StringBuilder();
        if(queryParams.getFlag().equals("0")){
            sql.append("select slope_number slopeNumber,st_area(ST_Transform(st_intersection((select geom from district_boundary_province where code='"+queryParams.getDivisionCode()+"'),geom),4527)) stArea from sys_slope_union where " +
                    "ST_Intersects(geom,(select geom from district_boundary_province where code='"+queryParams.getDivisionCode()+"')) GROUP BY slope_number,geom ORDER BY slope_number ");
        }else if(queryParams.getFlag().equals("1")){
            sql.append("select slope_number slopeNumber,st_area(ST_Transform(st_intersection((select geom from district_boundary_city where code='"+queryParams.getDivisionCode()+"'),geom),4527)) stArea from sys_slope_union where " +
                    "ST_Intersects(geom,(select geom from district_boundary_city where code='"+queryParams.getDivisionCode()+"')) GROUP BY slope_number,geom ORDER BY slope_number ");
        }else if(queryParams.getFlag().equals("2")){
            sql.append("select slope_number slopeNumber,st_area(ST_Transform(st_intersection((select geom from district_boundary_county where code='"+queryParams.getDivisionCode()+"'),geom),4527)) stArea from sys_slope_union where " +
                    "ST_Intersects(geom,(select geom from district_boundary_county where code='"+queryParams.getDivisionCode()+"')) GROUP BY slope_number,geom ORDER BY slope_number ");
        }
        List<ResultVo> resultVoList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResultVo.class));
        return resultVoList;
    }

    @Override
    public String getGeomByProvince(String provinceName) {
        try {
            String sql = "select st_astext(geom) as geom from district_boundary_province where name=? ";
            String geom = jdbcTemplate.queryForObject(sql,String.class,provinceName);
            return geom;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public String getGeomByCity(String provinceName, String cityName) {
        try {
            String sql = "select st_astext(b.geom) as geom from district_boundary_city b left join district_boundary_province c on c.code=concat(subString(b.code,0,3),'0000') where c.name=? and b.name=?";
            String geom = jdbcTemplate.queryForObject(sql,String.class,provinceName,cityName);
            return geom;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public String getGeomByArea(String provinceName, String cityName, String areaName) {
        try {
            String sql = "select st_astext(a.geom) as geom from district_boundary_county a left join district_boundary_city b on b.code=concat(subString(a.code,0,5),'00') left join district_boundary_province c on c.code=concat(subString(a.code,0,3),'0000') where c.name=? and b.name=? and a.name=?";
            String geom = jdbcTemplate.queryForObject(sql, String.class,provinceName,cityName,areaName);
            return geom;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public List<ResultVo> getSlopeAll() {
        String sql = "select slope_number slopeNumber,st_area(ST_Transform(geom,4527)) stArea from sys_slope_union GROUP BY slope_number,geom ORDER BY slope_number";
        List<ResultVo> resultVoList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ResultVo.class));
        return resultVoList;
    }

    @Override
    public List<ResultVo> getKilometreGridByDivision(QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select province,city,county area,population,coveredarea,gdp from data_pbe where isdelete='0' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        List<ResultVo> resultVoList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ResultVo.class));
        return resultVoList;
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from qh_map_files where  type ='地形地貌（坡度）_' and province = '" + province + "' and city = '" + city + "' and area = '" + county + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from qh_map_files where  type ='地形地貌（坡度）_' and province = '" + province + "' and city = '" + city + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from qh_map_files where  type ='地形地貌（坡度）_' and  province = '" + province + "' and is_delete ='0' ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
      //  return jdbcTemplate.queryForObject("SELECT count(t1.*) from sys_slope_union t1 LEFT JOIN qh_map_files t2 on t1.fileuuid = t2.uuid where t1.province = ? and (t2.update_time LIKE ? or t2.create_time LIKE ?)", Integer.class, provinceName, "%" + taskYear + "%", "%" + taskYear + "%");
        return jdbcTemplate.queryForObject("SELECT count(*) from qh_map_files t1  where type ='地形地貌（坡度）_' and  province = ? and (update_time LIKE ? or create_time LIKE ?)  and is_delete ='0'  ", Integer.class, provinceName, "%" + taskYear + "%", "%" + taskYear + "%");
    }

    @Override
    public ResultVo getDataByProvinceAndGeomText(String provinceName, String geomText) {
        try {
            String sql = "select sum(population) population ,sum(coveredarea) coveredarea,sum(gdp) gdp from data_pbe where isdelete='0' and province=? and ST_Intersects(st_geometryfromtext(?,4490) GROUP BY province";
            ResultVo resultVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResultVo.class),provinceName,geomText);
            return resultVo;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public ResultVo getDataByCityAndGeomText(String provinceName, String cityName, String geomText) {
        try {
            String sql = "select sum(population) population ,sum(coveredarea) coveredarea,sum(gdp) gdp from data_pbe where isdelete='0' and province=? and city=? and ST_Intersects(st_geometryfromtext(?,4490) GROUP BY city";
            ResultVo resultVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResultVo.class),provinceName,cityName,geomText);
            return resultVo;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public ResultVo getDataByAreaAndGeomText(String provinceName, String cityName, String areaName, String geomText) {
        try {
            String sql = "select sum(population) population ,sum(coveredarea) coveredarea,sum(gdp) gdp from data_pbe where isdelete='0' and province=? and city=? and county=? and ST_Intersects(st_geometryfromtext(?,4490) GROUP BY county";
            ResultVo resultVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResultVo.class),provinceName,cityName,areaName,geomText);
            return resultVo;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public List<BuildingEntity> getBuildingByGeomText(QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select province,city,county,seismic_capacity seismicCapacity from ypg_building_single where del_flag = '0'");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        List<BuildingEntity> buildingEntityList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingEntity.class));
        return buildingEntityList;
    }

  /*  @Override
    public List<ResultVo> getKilometreGridByGeomText(String geomText) {
        return null;
    }*/


    @Override
    public ResultVo  getDataByProvince(String provinceName) {
        try {
            String sql = "select sum(population) population ,sum(coveredarea) coveredarea,sum(gdp) gdp from data_pbe where isdelete='0' and province=? GROUP BY province";
            ResultVo resultVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResultVo.class),provinceName);
            return resultVo;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public ResultVo getDataByCity(String provinceName, String cityName) {
        try {
            String sql = "select sum(population) population ,sum(coveredarea) coveredarea,sum(gdp) gdp from data_pbe where isdelete='0' and province=? and city=? GROUP BY city";
            ResultVo resultVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResultVo.class),provinceName,cityName);
            return resultVo;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public ResultVo getDataByArea(String provinceName, String cityName, String areaName) {
        try {
            String sql = "select sum(population) population ,sum(coveredarea) coveredarea,sum(gdp) gdp from data_pbe where isdelete='0' and province=? and city=? and county=? GROUP BY county";
            ResultVo resultVo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(ResultVo.class),provinceName,cityName,areaName);
            return resultVo;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    @Override
    public int getAirport(QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select count(*) count from sys_airport where airport_status='1' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        return count;
    }

    @Override
    public int getReservoir(QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM data_reservoir where isdelete='1' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        return count;
    }

    @Override
    public int getSchool(String schoolType,QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM data_school where sch_isdelete='0' and sch_type like '%"+schoolType+"%' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        return count;
    }

    @Override
    public int getHospitalNum(String hospitalType,QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM data_hospital where dh_isdelete='1' and dh_type=? ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class,hospitalType);
        return count;
    }

    @Override
    public int getHospitalPower(String hospitalType,QueryParams queryParams) {
        try {
            StringBuilder sql = new StringBuilder("SELECT SUM(dh_power) FROM data_hospital where dh_isdelete='1' and dh_type=? ");
            if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
                sql.append(" and province = '"+queryParams.getProvince()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
                sql.append(" and city = '"+queryParams.getCity()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
                sql.append(" and county = '"+queryParams.getArea()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
                sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
            }
            StringBuilder groupSql = new StringBuilder("group by dh_type");
            String sum = jdbcTemplate.queryForObject(sql.append(groupSql).toString(), String.class,hospitalType);
            if(PlatformObjectUtils.isEmpty(sum)){
                return 0;
            }else{
                return Integer.parseInt(sum);
            }
        }catch (EmptyResultDataAccessException e){
            return 0;
        }
    }

    @Override
    public int getHospitalBedNum(String hospitalType,QueryParams queryParams) {
        try {
            StringBuilder sql = new StringBuilder("SELECT SUM(dh_bed_num) FROM data_hospital where dh_isdelete='1' and dh_type=? ");
            if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
                sql.append(" and province = '"+queryParams.getProvince()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
                sql.append(" and city = '"+queryParams.getCity()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
                sql.append(" and county = '"+queryParams.getArea()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
                sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
            }
            StringBuilder groupSql = new StringBuilder("group by dh_type");
            String sum = jdbcTemplate.queryForObject(sql.append(groupSql).toString(), String.class,hospitalType);
            if(PlatformObjectUtils.isEmpty(sum)){
                return 0;
            }else{
                return Integer.parseInt(sum);
            }
        }catch (EmptyResultDataAccessException e){
            return 0;
        }
    }

    @Override
    public int getDisastersNum(String disastersType,QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM data_geological_disasters where dgd_isdelete='0' and dgd_disaster_type=? ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class,disastersType);
        return count;
    }

    @Override
    public int getRiskEnterpriseNum(String riskGrade,QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM sys_riskenterprisedata where red_status='1' and red_riskgrade=? ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class,riskGrade);
        return count;
    }

    @Override
    public int getGasStationNum(String riskGrade,QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM sys_gasstationdata where gs_status='1' and gs_riskgrade=? ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class,riskGrade);

        return count;
    }

    @Override
    public int getEmergencyshelterByType(String type,QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM sys_emergencyshelter where es_status='1' and es_type=? ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class,type);
        return count;
    }

    @Override
    public int getPeopleTotalNumber(QueryParams queryParams) {
        try {
            StringBuilder sql = new StringBuilder("SELECT SUM(es_maxpeople) FROM sys_emergencyshelter where es_status='1' ");
            if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
                sql.append(" and province = '"+queryParams.getProvince()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
                sql.append(" and city = '"+queryParams.getCity()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
                sql.append(" and county = '"+queryParams.getArea()+"'");
            }
            String sum = jdbcTemplate.queryForObject(sql.toString(), String.class);
            if(PlatformObjectUtils.isEmpty(sum)){
                return 0;
            }else{
                return Integer.parseInt(sum);
            }
        }catch (EmptyResultDataAccessException e){
            return 0;
        }
    }

    @Override
    public int getBuildingNumByStructure(String structure,QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM ypg_building_single where del_flag='0' and building_structure=? ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class,structure);
        return count;
    }

    @Override
    public int getSamplingNum(QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("SELECT count(*) count FROM ypg_sampling_survey_new where del_flag='0' ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and area = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        return count;
    }

    @Override
    public int getDisasterRiskNum(String disasterType,QueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select count(*) FROM ypg_disaster_risk where del_flag='0' and disaster_type like '%"+disasterType+"%'");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
            sql.append(" and province = '"+queryParams.getProvince()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
            sql.append(" and city = '"+queryParams.getCity()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
            sql.append(" and county = '"+queryParams.getArea()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
            sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
        }
        int count = jdbcTemplate.queryForObject(sql.toString(), Integer.class);
        return count;
    }

    @Override
    public int getHouseNum(String type,QueryParams queryParams) {
        try {
            StringBuilder sql = new StringBuilder("select sum(kzt_shockresistancenumber) FROM sys_kangzhentu where kzt_status='1' and kzt_shockresistance =? ");
            if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())){
                sql.append(" and province = '"+queryParams.getProvince()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getCity())){
                sql.append(" and city = '"+queryParams.getCity()+"'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getArea())){
//                sql.append(" and county = '"+queryParams.getArea()+"'");
                sql.append(" and kzt_counties = '"+queryParams.getArea()+"'");//上传文件属性表的区县字段值
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getGeomText())){
                sql.append(" and ST_Intersects(geom,(st_geometryfromtext('"+queryParams.getGeomText()+"',4490)))");
            }
            String sum = jdbcTemplate.queryForObject(sql.toString(), String.class,type);
            if(PlatformObjectUtils.isEmpty(sum)){
                return 0;
            }else{
                return Integer.parseInt(sum);
            }
        }catch (EmptyResultDataAccessException e){
            return  0;
        }
    }




}
